package com.dao;

import java.sql.*;
import java.util.*;
import java.math.*;
import com.vo.*;

public class ClientesDAO {
	
	private Connection connection;

	/**
	 * private ctor
	 */
	private ClientesDAO(Connection connection) {
		this.connection = connection;
	}
	
	/**
	 * Factory method
	 */
	static public ClientesDAO create(Connection connection) {
		return new ClientesDAO(connection);
	}
	/**
	* retrieve one instance of the bean identified by the primary key values passed
	* as arguments
	*/
    public Clientes retrieve(Integer idCliente)	throws SQLException {
		
    	String sql = "SELECT  id_cliente, nombre, direccion, telefono, id_esquina1, id_esquina2 FROM clientes WHERE 1 = 1 "
		 + "AND id_cliente = ? ";
		PreparedStatement stmt = null;
		Clientes ret = null;
		int idx = 1;
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setObject(idx++,idCliente,Types.INTEGER);			
			ResultSet rs = stmt.executeQuery();
			if(!rs.next()) {
				return null;
			}
			ret = makeObject(rs);
			rs.close();
		}finally{
			if(stmt != null)
		  		stmt.close();
		}
		return ret;
    }
    
    public Clientes retrieve(String telefono) throws SQLException {
		
    	String sql = "SELECT  id_cliente, nombre, direccion, telefono, id_esquina1, id_esquina2 FROM clientes WHERE 1 = 1 "
		 + "AND telefono = ? ";
		PreparedStatement stmt = null;
		Clientes ret = null;
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, telefono);			
			ResultSet rs = stmt.executeQuery();
			if(!rs.next()) {
				return null;
			}
			ret = makeObject(rs);
			rs.close();
		}finally{
			if(stmt != null)
		  		stmt.close();
		}
		return ret;
    }

	/**
	* Create a new record in the table

 
    * 

    */
    public int insert(Clientes bean) throws SQLException {
    	  validate(bean);
    	      
          String sql = "";
          PreparedStatement stmt = null;

          try {
               sql = "INSERT INTO clientes (nombre, direccion, telefono, id_esquina1, id_esquina2) " + 
               	"VALUES (?  , ?  , ?  , ?  , ?  ) ";
			stmt = connection.prepareStatement(sql);
			int idx = 1;
			stmt.setString(idx++,bean.getNombre());
			stmt.setString(idx++,bean.getDireccion());
			stmt.setString(idx++,bean.getTelefono());
			stmt.setObject(idx++,bean.getIdEsquina1(),Types.INTEGER);
			stmt.setObject(idx++,bean.getIdEsquina2(),Types.INTEGER);
			int rowcount = stmt.executeUpdate();
			return rowcount;
          }finally{
              if (stmt != null)
                  stmt.close();
          }
    }

	/**

 
    * 

    */
    public int[] insertAll(Collection beans) throws SQLException {
          PreparedStatement stmt = null;
          String sql = "INSERT INTO clientes (   id_cliente  , nombre  , direccion  , telefono  , id_esquina1  , id_esquina2  ) " + 
               	"VALUES (  ?  , ?  , ?  , ?  , ?  , ?  ) ";
		  stmt = connection.prepareStatement(sql);
          try {
			  for (Iterator it = beans.iterator(); it.hasNext();) {
		      	Clientes bean = (Clientes)it.next();
	    	  	validate(bean);	    	      
				int idx = 1;
				stmt.setObject(idx++,bean.getIdCliente(),Types.INTEGER);
				stmt.setString(idx++,bean.getNombre());
				stmt.setString(idx++,bean.getDireccion());
				stmt.setString(idx++,bean.getTelefono());
				stmt.setObject(idx++,bean.getIdEsquina1(),Types.INTEGER);
				stmt.setObject(idx++,bean.getIdEsquina2(),Types.INTEGER);
								
				stmt.addBatch();
			}
			return stmt.executeBatch();
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }
	
	/**

 
    * 

    */
    public int[] updateAll(Collection beans) throws SQLException {
          PreparedStatement stmt = null;
          String sql = "UPDATE clientes SET " + 
							" id_cliente = ? " +
							",  nombre = ? " +
							",  direccion = ? " +
							",  telefono = ? " +
							",  id_esquina1 = ? " +
							",  id_esquina2 = ? " +
							" WHERE "  
							+ " id_cliente = ? ";
		  stmt = connection.prepareStatement(sql);
          try {
			  for (Iterator it = beans.iterator(); it.hasNext();) {
		      	Clientes bean = (Clientes)it.next();
	    	  	validate(bean);	    	      
				int idx = 1;
				stmt.setObject(idx++,bean.getIdCliente(),Types.INTEGER);
				stmt.setString(idx++,bean.getNombre());
				stmt.setString(idx++,bean.getDireccion());
				stmt.setString(idx++,bean.getTelefono());
				stmt.setObject(idx++,bean.getIdEsquina1(),Types.INTEGER);
				stmt.setObject(idx++,bean.getIdEsquina2(),Types.INTEGER);
				
			  stmt.setObject(idx++,bean.getIdCliente(),Types.INTEGER);			  				
				stmt.addBatch();
			}
			return stmt.executeBatch();
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }
	/**

 
    * 

    */
    public int update(Clientes bean) throws SQLException {
    	
    	validate(bean);
    	String sql = "UPDATE clientes SET " + 
							" id_cliente = ? " +
							",  nombre = ? " +
							",  direccion = ? " +
							",  telefono = ? " +
							",  id_esquina1 = ? " +
							",  id_esquina2 = ? " +
							" WHERE "  
							+ " id_cliente = ? ";
				
          PreparedStatement stmt = null;
		  int idx = 1;
          try {
              stmt = connection.prepareStatement(sql);
			  stmt.setObject(idx++,bean.getIdCliente(),Types.INTEGER);
			  stmt.setString(idx++,bean.getNombre());
			  stmt.setString(idx++,bean.getDireccion());
			  stmt.setString(idx++,bean.getTelefono());
			  stmt.setObject(idx++,bean.getIdEsquina1(),Types.INTEGER);
			  stmt.setObject(idx++,bean.getIdEsquina2(),Types.INTEGER);
			  
			  stmt.setObject(idx++,bean.getIdCliente(),Types.INTEGER);			  
              int rowcount = stmt.executeUpdate();
			  return rowcount;
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }



	/**

 
    * 

    */
    public int delete(Clientes bean) throws SQLException {
          String sql = "DELETE FROM clientes WHERE 1=1 "
							+ " AND id_cliente = ? "
			;
			
          PreparedStatement stmt = null;
		  int idx = 1;
          try {
              stmt = connection.prepareStatement(sql);
			  stmt.setObject(idx++,bean.getIdCliente(),Types.INTEGER);
			  
              int rowcount = stmt.executeUpdate();
			return rowcount;
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }


	/**

 
    * 

    */
    public int deleteAll() throws SQLException {

          String sql = "DELETE FROM clientes";
          PreparedStatement stmt = null;
          try {
              stmt = connection.prepareStatement(sql);
              int rowcount = stmt.executeUpdate();
              return rowcount;
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }

 
    public int countAll() throws SQLException {

          String sql = "SELECT count(*) FROM clientes";
          PreparedStatement stmt = null;
          ResultSet result = null;
          int allRows = 0;

          try {
              stmt = connection.prepareStatement(sql);
              result = stmt.executeQuery();
              if (result.next())
                  allRows = result.getInt(1);
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }
          return allRows;
    }


	/**

 
    * 

    */
    public Collection retrieveAll(Clientes bean) throws SQLException {
          List searchResults;
          StringBuffer sql = new StringBuffer("SELECT * FROM clientes WHERE 1=1 ");

		  if (bean.getIdCliente() != null ) {
		    	sql.append( " AND id_cliente=? " );
			}
		  if (bean.getNombre() != null ) {
		    	sql.append( " AND nombre=? " );
			}
		  if (bean.getDireccion() != null ) {
		    	sql.append( " AND direccion=? " );
			}
		  if (bean.getTelefono() != null ) {
		    	sql.append( " AND telefono=? " );
			}
		  if (bean.getIdEsquina1() != null ) {
		    	sql.append( " AND id_esquina1=? " );
			}
		  if (bean.getIdEsquina2() != null ) {
		    	sql.append( " AND id_esquina2=? " );
			}
		  
			PreparedStatement stmt = connection.prepareStatement(sql.toString());
			int idx = 1;			
		  if (bean.getIdCliente() != null ) {
	      		stmt.setObject(idx++,bean.getIdCliente(),Types.INTEGER);}			    
		  if (bean.getNombre() != null ) {
	      		stmt.setString(idx++,bean.getNombre());}			    
		  if (bean.getDireccion() != null ) {
	      		stmt.setString(idx++,bean.getDireccion());}			    
		  if (bean.getTelefono() != null ) {
	      		stmt.setString(idx++,bean.getTelefono());}			    
		  if (bean.getIdEsquina1() != null ) {
	      		stmt.setObject(idx++,bean.getIdEsquina1(),Types.INTEGER);}			    
		  if (bean.getIdEsquina2() != null ) {
	      		stmt.setObject(idx++,bean.getIdEsquina2(),Types.INTEGER);}			    
		  
		 ResultSet rs = stmt.executeQuery();
		 if (rs.next()) {
		     searchResults = new ArrayList();
		 } else {
		 	return Collections.EMPTY_LIST;
		 }
		 Clientes d;
		 do {
		 	d = makeObject( rs );
		 	searchResults.add( d );
		 } while (rs.next());

          return searchResults;
    }



	/**

 
    * 

    */
    static private Clientes makeObject(ResultSet result) throws SQLException {
          Clientes bean = new Clientes();
/*
		  Integer id_cliente result;
		  String nombre result;
		  String direccion result;
		  String telefono result;
		  Integer id_esquina1 result;
		  Integer id_esquina2 result;
*/
		  bean.setIdCliente((Integer)get(Integer.class, result.getObject("id_cliente")));
		  bean.setNombre(result.getString("nombre"));
		  bean.setDireccion(result.getString("direccion"));
		  bean.setTelefono(result.getString("telefono"));
		  bean.setIdEsquina1((Integer)get(Integer.class, result.getObject("id_esquina1")));
		  bean.setIdEsquina2((Integer)get(Integer.class, result.getObject("id_esquina2")));
		return bean;
    }


	/**

 
    * 

    */
    static public Object get(Class cls, Object source) {
        if (source == null) return null;
        if (cls.isInstance( source.getClass())) return source;
        if (cls.equals(String.class)) return source.toString();
        if (source instanceof Number) {
            Number n = (Number)source;
            if (cls.equals(Integer.class)) return new Integer( n.intValue() );
            if (cls.equals(Long.class)) return new Long( n.longValue() );
            if (cls.equals(Float.class)) return new Float( n.floatValue() );
            if (cls.equals(Double.class)) return new Double( n.doubleValue() );
            if (cls.equals(Short.class)) return new Short( n.shortValue() );
            if (cls.equals(Byte.class)) return new Byte( n.byteValue() );
            if (cls.equals(BigDecimal.class)) return new BigDecimal( n.doubleValue() );
        }

        if (cls.equals( Character.class)) {
            String str = source.toString();
            if (str.length() == 0) return null;
            return new Character(str.charAt( 0 ));
        }
        
        return source;
    }
    
    public Collection<Clientes> retrieveLike(String cli) throws SQLException {
        List searchResults;
        StringBuffer sql = new StringBuffer("SELECT * FROM clientes WHERE 1=1 ");
        	sql.append( " AND LOWER(nombre) LIKE ? or telefono=?" );
			PreparedStatement stmt = connection.prepareStatement(sql.toString());
			int idx = 1;
	      	stmt.setString(idx++,"%"+cli+"%");
	      	idx++;
	      	stmt.setString(idx++,"%"+cli+"%");
		 ResultSet rs = stmt.executeQuery();
		 if (rs.next()) {
		     searchResults = new ArrayList();
		 } else {
		 	return Collections.EMPTY_LIST;
		 }
		 Clientes d;
		 do {
		 	d = makeObject( rs );
		 	searchResults.add( d );
		 } while (rs.next());

        return searchResults;
    }

	/**

     
    * 

    */
	private void validate( Clientes bean ) throws SQLException {
	// NULL values
		if (bean.getIdCliente() == null) {
			//throw new SQLException("Column id_cliente, property idCliente of type Clientes cannot be NULL");
		}
 		if (bean.getNombre() == null) {
			throw new SQLException("Column nombre, property nombre of type Clientes cannot be NULL");
		}
 		if (bean.getNombre() != null && bean.getNombre().length() > 150 ) {
			throw new SQLException("Column nombre, property nombre of type Clientes should be less than 150 chars");
		}
		if (bean.getDireccion() == null) {
			throw new SQLException("Column direccion, property direccion of type Clientes cannot be NULL");
		}
 		if (bean.getDireccion() != null && bean.getDireccion().length() > 150 ) {
			throw new SQLException("Column direccion, property direccion of type Clientes should be less than 150 chars");
		}
		if (bean.getTelefono() == null) {
			throw new SQLException("Column telefono, property telefono of type Clientes cannot be NULL");
		}
 		if (bean.getTelefono() != null && bean.getTelefono().length() > 50 ) {
			throw new SQLException("Column telefono, property telefono of type Clientes should be less than 50 chars");
		}
  	}
	
	


}


             



